package cn.tuzirpa.tracker;

import cn.tuzirpa.manager.DatabaseManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * SQL执行记录跟踪器
 * 用于跟踪已执行的SQL文件，避免重复执行
 */
public class SqlExecutionTracker {
    
    private static final Logger logger = LoggerFactory.getLogger(SqlExecutionTracker.class);
    
    private static final String EXECUTION_LOG_TABLE = "sql_execution_log";
    
    private final DatabaseManager databaseManager;
    
    public SqlExecutionTracker(DatabaseManager databaseManager) {
        this.databaseManager = databaseManager;
        initializeExecutionLogTable();
    }
    
    /**
     * 初始化执行记录表
     */
    private void initializeExecutionLogTable() {
        String createTableSql = String.format(
            "CREATE TABLE IF NOT EXISTS `%s` (" +
            "`id` BIGINT AUTO_INCREMENT PRIMARY KEY, " +
            "`file_name` VARCHAR(255) NOT NULL, " +
            "`file_path` VARCHAR(500) NOT NULL, " +
            "`file_hash` VARCHAR(64) NOT NULL, " +
            "`execution_time` DATETIME NOT NULL, " +
            "`execution_status` VARCHAR(20) NOT NULL, " +
            "`error_message` TEXT, " +
            "`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
            "UNIQUE KEY `uk_file_hash` (`file_hash`), " +
            "KEY `idx_file_name` (`file_name`), " +
            "KEY `idx_execution_time` (`execution_time`) " +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            EXECUTION_LOG_TABLE
        );
        
        try (Connection connection = databaseManager.getConnection();
             Statement statement = connection.createStatement()) {
            
            statement.execute(createTableSql);
            logger.info("执行记录表 {} 初始化完成", EXECUTION_LOG_TABLE);
            
        } catch (SQLException e) {
            logger.error("初始化执行记录表失败", e);
            throw new RuntimeException("初始化执行记录表失败", e);
        }
    }
    
    /**
     * 检查SQL文件是否已执行过
     */
    public boolean isFileExecuted(String fileHash) {
        String sql = String.format(
            "SELECT COUNT(*) FROM `%s` WHERE `file_hash` = ? AND `execution_status` = 'SUCCESS'",
            EXECUTION_LOG_TABLE
        );
        
        try (Connection connection = databaseManager.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            
            statement.setString(1, fileHash);
            
            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    int count = resultSet.getInt(1);
                    return count > 0;
                }
            }
            
        } catch (SQLException e) {
            logger.error("检查文件执行状态失败: {}", fileHash, e);
            throw new RuntimeException("检查文件执行状态失败", e);
        }
        
        return false;
    }
    
    /**
     * 记录SQL文件执行开始
     */
    public void recordExecutionStart(String fileName, String filePath, String fileHash) {
        String sql = String.format(
            "INSERT INTO `%s` (`file_name`, `file_path`, `file_hash`, `execution_time`, `execution_status`) " +
            "VALUES (?, ?, ?, ?, 'STARTED') " +
            "ON DUPLICATE KEY UPDATE " +
            "`execution_time` = VALUES(`execution_time`), " +
            "`execution_status` = 'STARTED', " +
            "`error_message` = NULL",
            EXECUTION_LOG_TABLE
        );
        
        try (Connection connection = databaseManager.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            
            statement.setString(1, fileName);
            statement.setString(2, filePath);
            statement.setString(3, fileHash);
            statement.setString(4, LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
            
            statement.executeUpdate();
            logger.debug("记录SQL文件执行开始: {}", fileName);
            
        } catch (SQLException e) {
            logger.error("记录SQL文件执行开始失败: {}", fileName, e);
            throw new RuntimeException("记录SQL文件执行开始失败", e);
        }
    }
    
    /**
     * 记录SQL文件执行成功
     */
    public void recordExecutionSuccess(String fileHash) {
        String sql = String.format(
            "UPDATE `%s` SET `execution_status` = 'SUCCESS', `error_message` = NULL " +
            "WHERE `file_hash` = ?",
            EXECUTION_LOG_TABLE
        );
        
        try (Connection connection = databaseManager.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            
            statement.setString(1, fileHash);
            
            int updatedRows = statement.executeUpdate();
            if (updatedRows > 0) {
                logger.debug("记录SQL文件执行成功: {}", fileHash);
            } else {
                logger.warn("未找到对应的执行记录: {}", fileHash);
            }
            
        } catch (SQLException e) {
            logger.error("记录SQL文件执行成功失败: {}", fileHash, e);
            throw new RuntimeException("记录SQL文件执行成功失败", e);
        }
    }
    
    /**
     * 记录SQL文件执行失败
     */
    public void recordExecutionFailure(String fileHash, String errorMessage) {
        String sql = String.format(
            "UPDATE `%s` SET `execution_status` = 'FAILED', `error_message` = ? " +
            "WHERE `file_hash` = ?",
            EXECUTION_LOG_TABLE
        );
        
        try (Connection connection = databaseManager.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            
            statement.setString(1, errorMessage);
            statement.setString(2, fileHash);
            
            int updatedRows = statement.executeUpdate();
            if (updatedRows > 0) {
                logger.debug("记录SQL文件执行失败: {}", fileHash);
            } else {
                logger.warn("未找到对应的执行记录: {}", fileHash);
            }
            
        } catch (SQLException e) {
            logger.error("记录SQL文件执行失败失败: {}", fileHash, e);
            throw new RuntimeException("记录SQL文件执行失败失败", e);
        }
    }
    
    /**
     * 获取执行历史记录
     */
    public void printExecutionHistory() {
        String sql = String.format(
            "SELECT `file_name`, `file_path`, `execution_time`, `execution_status`, `error_message` " +
            "FROM `%s` ORDER BY `execution_time` DESC LIMIT 20",
            EXECUTION_LOG_TABLE
        );
        
        try (Connection connection = databaseManager.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            
            logger.info("=== SQL文件执行历史记录 ===");
            logger.info("文件名\t\t执行时间\t\t\t状态\t\t错误信息");
            logger.info("------------------------------------------------------------");
            
            while (resultSet.next()) {
                String fileName = resultSet.getString("file_name");
                String executionTime = resultSet.getString("execution_time");
                String status = resultSet.getString("execution_status");
                String errorMessage = resultSet.getString("error_message");
                
                logger.info("{}\t{}\t{}\t{}", 
                    fileName, executionTime, status, 
                    errorMessage != null ? errorMessage : "");
            }
            
        } catch (SQLException e) {
            logger.error("获取执行历史记录失败", e);
            throw new RuntimeException("获取执行历史记录失败", e);
        }
    }
    
    /**
     * 清理执行记录（可选功能）
     */
    public void clearExecutionHistory() {
        String sql = String.format("DELETE FROM `%s`", EXECUTION_LOG_TABLE);
        
        try (Connection connection = databaseManager.getConnection();
             Statement statement = connection.createStatement()) {
            
            int deletedRows = statement.executeUpdate(sql);
            logger.info("清理了 {} 条执行记录", deletedRows);
            
        } catch (SQLException e) {
            logger.error("清理执行记录失败", e);
            throw new RuntimeException("清理执行记录失败", e);
        }
    }
}
